#!/bin/bash
APP=edu

# 如果输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi
dws_interaction_questiontest_grades_1d="
insert overwrite table dws_interaction_questiontest_grades_1d partition('dt=$do_date')
select
       user_id,
       paper_title,
       course_name,
       score,
	   duration_sec,
	   dt
from dwd_interaction_questiontest_inc
where dt='$do_date'
  group by
user_id, paper_title, course_name,score,duration_sec;"
dws_traffic_session_page_view_1d="
insert overwrite table dws_traffic_session_page_view_1d partition(dt='$do_date')
select
    session_id,
    mid_id,
    brand,
    model,
    operate_system,
    version_code,
    source_type,
    sum(during_time),
    count(*)
from dwd_traffic_page_view_inc
where dt='$do_date'
group by session_id,mid_id,brand,model,operate_system,version_code,channel;"
dws_traffic_page_visitor_page_view_1d="
insert overwrite table dws_traffic_page_visitor_page_view_1d partition(dt='$do_date')
select
    mid_id,
    brand,
    model,
    operate_system,
    page_id,
    sum(during_time),
    count(*)
from dwd_traffic_page_view_inc
where dt='$do_date'
group by mid_id,brand,model,operate_system,page_id;"
dws_study_course_avg_stars_1d="
insert overwrite table dws_study_course_avg_stars_1d partition(dt='$do_date')
select
    course_id,
    total_review_stars/cnt as 1d_avg_stars,
    user_cnt,
    5_stars_cnt/cnt as 5_stars_rate
from (
    select
        course_id,
        count(*) as cnt,
        count(distinct user_id) as user_cnt,
        sum(review_stars) total_review_stars,
        sum(if(review_stars=5,1,0)) 5_stars_cnt
    from dwd_review_info_inc
    where dt = '$do_date'
    group by course_id   
)t1;"
dws_traffic_source_order_payment_1d="
INSERT OVERWRITE TABLE dws_traffic_source_order_payment_1d PARTITION (dt='$do_date')
SELECT
    sc,
    source_site,
    sum(total_amount) total_amount_1d,
    count(distinct t2.uid) user_count_1d,
    count(distinct t3.user_id) order_count_1d,
    t1.dt
FROM (
      SELECT id,
             source_site,
             dt
      FROM ods_base_source_full
	  where dt='$do_date'
      GROUP BY source_site, id, dt
         )t1
LEFT JOIN (
      SELECT uid,
             sc,
             dt
      FROM dwd_traffic_page_view_inc
	  	  where dt='$do_date'
      GROUP BY uid, sc, dt
    )t2
ON t1.id = t2.sc
LEFT JOIN (
      SELECT user_id,
             total_amount,
             dt
      FROM dwd_trade_pay_detail_suc_inc
	  	  where dt='$do_date'
      GROUP BY user_id, total_amount, dt
    )t3
ON t2.uid = t3.user_id
GROUP BY sc, source_site,t1.dt;"
dws_video_audition_play_1d="
insert overwrite table dws_video_audition_play_1d partition (dt='$do_date')
select
       course_id,
       course_name,
       subject_id,
       subject_name,
       category_id,
       category_name,
       count(user_id) user_count,
       sum(order_num) audition_sum
from (
         select t1.user_id,
                t1.course_id,
                course_name,
                subject_id,
                subject_name,
                category_id,
                category_name,
                if(t2.order_id is not null, 1, 0) order_num
         from (
                  select user_id,
                         course_id,
                         course_name,
                         subject_id,
                         subject_name,
                         category_id,
                         category_name
                  from dwd_video_audition_play_inc
                  where dt = '$do_date'
                    and is_first = 1
              ) t1
                  left join (
             select user_id,
                    course_id,
                    id order_id
             from dwd_trade_order_detail_inc
             where dt = '$do_date'
         ) t2 on t1.user_id = t2.user_id and t1.course_id = t2.course_id
     ) t3
group by course_id,course_name,subject_id,subject_name,category_id,category_name
order by course_id;"
dws_interaction_questiontest_grades_1d="
insert overwrite table dws_interaction_questiontest_grades_1d partition('dt=$do_date')
select
       user_id,
       paper_title,
       course_name,
       score,
	   duration_sec,
	   dt
from dwd_interaction_questiontest_inc
where dt='$do_date'
  group by
user_id, paper_title, course_name,score,duration_sec;"
case $1 in
    "dws_interaction_questiontest_grades_1d" )
        hive -e "$dws_interaction_questiontest_grades_1d"
    ;;
    "dws_traffic_session_page_view_1d" )
        hive -e "$dws_traffic_session_page_view_1d"
    ;;
    "dws_traffic_page_visitor_page_view_1d" )
        hive -e "$dws_traffic_page_visitor_page_view_1d"
    ;;
    "dws_study_course_avg_stars_1d" )
        hive -e "$dws_study_course_avg_stars_1d"
    ;;
    "dws_traffic_source_order_payment_1d" )
        hive -e "$dws_traffic_source_order_payment_1d"
    ;;
    "dws_video_audition_play_1d" )
        hive -e "$dws_video_audition_play_1d"
    ;;
    "dws_interaction_questiontest_grades_1d" )
        hive -e "$dws_interaction_questiontest_grades_1d"
    ;;
   "all" )
    hive -e "$dws_interaction_questiontest_grades_1d
	$dws_traffic_session_page_view_1d
	$dws_traffic_page_visitor_page_view_1d
	$dws_trade_user_payment_1d
	$dws_traffic_source_order_payment_1d
	$dws_video_audition_play_1d
	$dws_interaction_questiontest_grades_1d"
    ;;
esac
